package Server.BusinessObjects;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Server.DataAccess.ConnectJDBCMySQL;

public class Category {

	private int id;
	private String name;

	public Category() {

	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}


	public Category(ResultSet rs) throws SQLException {
		this.setId(rs.getInt("Id"));
		this.setName(rs.getString("Name"));
	}

	public static boolean existsInDb(String categoryName) {
		ArrayList<Category> categories;
		try {
			categories = (ArrayList<Category>) Category.getAllCategories();

			for (Category c : categories) {
				if (c.getName().equalsIgnoreCase(categoryName)) {
					return true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	public static boolean existsInScenario(int scenarioId, int categoryId) {
		ArrayList<Category> categories;
		try {
			categories = (ArrayList<Category>) Scenario
					.getCategoriesToScenario(scenarioId);

			for (Category c : categories) {
				if (categoryId == c.getId()) {
					return true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	public static Category getCategory(int id) throws SQLException {
		String query = "Select * from categories where Id = ?";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);
		ps.setInt(1, id);

		List<Category> list = Scenario.getCategoryList(ps);

		return list.size() == 1 ? list.get(0) : null;
	}

	public static List<Category> getAllCategories() throws SQLException {
		String query = "Select * from categories";
		PreparedStatement ps = ConnectJDBCMySQL.getConnection()
				.prepareStatement(query);

		return Scenario.getCategoryList(ps);
	}

	public static List<Integer> getSeveritiesToCategory(int categoryId)
			throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "Select severity from questions q, queCat qc Where q.id = qc.queId and qc.catId=?";

		PreparedStatement ps = dbCon.prepareStatement(query);

		ps.setInt(1, categoryId);

		List<Integer> list = Category.getSeverityList(ps);

		return list;
	}

	public static List<Scenario> getScenariosToCategory(int categoryId)
			throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "Select * from scenarios s, catscen cs Where s.id = cs.scenId and cs.catId=?";

		PreparedStatement ps = dbCon.prepareStatement(query);

		ps.setInt(1, categoryId);

		List<Scenario> list = Scenario.getScenarioList(ps);

		return list;
	}

	private static List<Integer> getSeverityList(PreparedStatement ps)
			throws SQLException {
		List<Integer> output = new ArrayList<Integer>();

		try {

			ResultSet rs = ps.executeQuery();

			while (rs.next())
				output.add(rs.getInt("severity"));

			rs.close();
		} finally {
			Connection con = ps.getConnection();
			if (ps != null)
				ps.close();

			if (con != null)
				con.close();

		}
		return output;
	}

	public static int insertCategoryInDb(int scenarioId, String categoryName)
			throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "INSERT INTO categories (name) VALUES (?);";
		PreparedStatement ps = dbCon.prepareStatement(query);

		ps.setString(1, categoryName);
		ps.execute();

		query = "SELECT LAST_INSERT_ID();";
		ps = dbCon.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		rs.next();
		int newId = rs.getInt(1);

		query = "INSERT INTO catscen (catId, scenId) VALUES (?,?);";
		ps = dbCon.prepareStatement(query);
		ps.setInt(1, newId);
		ps.setInt(2, scenarioId);
		ps.execute();
		// autocommit is on by default
		dbCon.close();

		return newId;
	}

	public static void addCategoryToScenario(int scenarioId, int categoryId)
			throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "INSERT INTO catscen (catId, scenId) VALUES (?,?);";
		PreparedStatement ps = dbCon.prepareStatement(query);
		ps.setInt(1, categoryId);
		ps.setInt(2, scenarioId);
		ps.execute();
		dbCon.close();

	}

	public static void deleteCategoryFromDb(int categoryId) throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "DELETE FROM quecat WHERE catId=?;";
		PreparedStatement ps = dbCon.prepareStatement(query);
		ps.setInt(1, categoryId);
		ps.execute();

		query = "DELETE FROM catscen WHERE catId=?;";
		ps = dbCon.prepareStatement(query);
		ps.setInt(1, categoryId);
		ps.execute();
		
		query = "DELETE FROM highscores WHERE catId=?;";
		ps = dbCon.prepareStatement(query);
		ps.setInt(1, categoryId);
		ps.execute();

		query = "DELETE FROM categories WHERE id=?;";
		ps = dbCon.prepareStatement(query);
		ps.setInt(1, categoryId);
		ps.execute();

		dbCon.close();

	}

	public static void updateCategoryInDb(int categoryId, String newCategoryName)
			throws SQLException {

		Category c = Category.getCategory(categoryId);

		if (newCategoryName.equalsIgnoreCase("null")) {
			newCategoryName = c.getName();
		}

		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "UPDATE categories SET name=? WHERE id=?";
		PreparedStatement ps = dbCon.prepareStatement(query);
		ps.setString(1, newCategoryName);
		ps.setInt(2, categoryId);
		ps.execute();

		dbCon.close();

	}

	public static List<Category> getList(PreparedStatement ps)
			throws SQLException {
		List<Category> output = new ArrayList<Category>();

		try {
			ResultSet rs = ps.executeQuery();

			while (rs.next())
				output.add(new Category(rs));

			rs.close();
		} finally {
			Connection con = ps.getConnection();
			if (ps != null)
				ps.close();

			if (con != null)
				con.close();

		}

		return output;
	}

	public static void deleteCategoryFromScenario(int categoryId, int scenarioId)
			throws SQLException {
		Connection dbCon = ConnectJDBCMySQL.getConnection();

		String query = "DELETE FROM catscen WHERE catId=? AND scenId=?;";
		PreparedStatement ps = dbCon.prepareStatement(query);
		ps.setInt(1, categoryId);
		ps.setInt(2, scenarioId);
		ps.execute();

		dbCon.close();

	}

}
